<!DOCTYPE html>
<html lang="en-US">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>树形结构数据存储方案 | 万事不如杯在手,一年几见月当头</title>
    <meta name="generator" content="VuePress 1.4.1">
    <link 0="l" 1="i" 2="n" 3="k">[object Object]
    <meta name="description" content="who else em ?
">
    <meta name="viewport" content="width=device-width,initial-scale=1,user-scalable=no">
    <link rel="preload" href="/assets/css/0.styles.4b594c7e.css" as="style"><link rel="preload" href="/assets/js/app.0dee73fb.js" as="script"><link rel="preload" href="/assets/js/3.0d533a50.js" as="script"><link rel="preload" href="/assets/js/1.0c609987.js" as="script"><link rel="preload" href="/assets/js/25.533c713d.js" as="script"><link rel="prefetch" href="/assets/js/10.3316d455.js"><link rel="prefetch" href="/assets/js/11.2b2a499c.js"><link rel="prefetch" href="/assets/js/12.6a7a8fbf.js"><link rel="prefetch" href="/assets/js/13.582b5c22.js"><link rel="prefetch" href="/assets/js/14.d1bdc2de.js"><link rel="prefetch" href="/assets/js/15.fd78a25a.js"><link rel="prefetch" href="/assets/js/16.a0698a19.js"><link rel="prefetch" href="/assets/js/17.ad4b3410.js"><link rel="prefetch" href="/assets/js/18.e7b3862e.js"><link rel="prefetch" href="/assets/js/19.3eadff44.js"><link rel="prefetch" href="/assets/js/20.e0f4fe17.js"><link rel="prefetch" href="/assets/js/21.7b915f15.js"><link rel="prefetch" href="/assets/js/22.c31c7474.js"><link rel="prefetch" href="/assets/js/23.5851acd3.js"><link rel="prefetch" href="/assets/js/24.b174dedc.js"><link rel="prefetch" href="/assets/js/26.e6bcabe4.js"><link rel="prefetch" href="/assets/js/27.849c96b7.js"><link rel="prefetch" href="/assets/js/28.7a16bb98.js"><link rel="prefetch" href="/assets/js/4.ab49d4f3.js"><link rel="prefetch" href="/assets/js/5.e19d1c20.js"><link rel="prefetch" href="/assets/js/6.5e86b234.js"><link rel="prefetch" href="/assets/js/7.df04ee3d.js"><link rel="prefetch" href="/assets/js/8.e01c6469.js"><link rel="prefetch" href="/assets/js/9.e273ccc3.js">
    <link rel="stylesheet" href="/assets/css/0.styles.4b594c7e.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><div class="theme-container" data-v-19557b78><div data-v-19557b78><div id="loader-wrapper" class="loading-wrapper" data-v-d48f4d20 data-v-19557b78 data-v-19557b78><div class="loader-main" data-v-d48f4d20><div data-v-d48f4d20></div><div data-v-d48f4d20></div><div data-v-d48f4d20></div><div data-v-d48f4d20></div></div> <!----> <!----></div> <div class="password-shadow password-wrapper-out" style="display:none;" data-v-64685f0e data-v-19557b78 data-v-19557b78><h3 class="title" style="display:none;" data-v-64685f0e data-v-64685f0e>万事不如杯在手,一年几见月当头</h3> <!----> <label id="box" class="inputBox" style="display:none;" data-v-64685f0e data-v-64685f0e><input type="password" value="" data-v-64685f0e> <span data-v-64685f0e>Konck! Knock!</span> <button data-v-64685f0e>OK</button></label> <div class="footer" style="display:none;" data-v-64685f0e data-v-64685f0e><span data-v-64685f0e><i class="iconfont reco-theme" data-v-64685f0e></i> <a target="blank" href="https://vuepress-theme-reco.recoluan.com" data-v-64685f0e>vuePress-theme-reco</a></span> <span data-v-64685f0e><i class="iconfont reco-copyright" data-v-64685f0e></i> <a data-v-64685f0e><span data-v-64685f0e>zhairuihao</span>
            
          <!---->
          2020
        </a></span></div></div> <div class="hide" data-v-19557b78><header class="navbar" data-v-19557b78><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/" class="home-link router-link-active"><img src="/logo.png" alt="万事不如杯在手,一年几见月当头" class="logo"> <span class="site-name">万事不如杯在手,一年几见月当头</span></a> <div class="links"><div class="color-picker"><a class="color-button"><i class="iconfont reco-color"></i></a> <div class="color-picker-menu" style="display:none;"><div class="mode-options"><h4 class="title">Choose mode</h4> <ul class="color-mode-options"><li class="dark">dark</li><li class="auto active">auto</li><li class="light">light</li></ul></div></div></div> <div class="search-box"><i class="iconfont reco-search"></i> <input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/" class="nav-link"><i class="iconfont reco-home"></i>
  Home
</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-category"></i>
      Category
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/categories/JavaScript/" class="nav-link"><i class="iconfont undefined"></i>
  JavaScript
</a></li><li class="dropdown-item"><!----> <a href="/categories/Java/" class="nav-link"><i class="iconfont undefined"></i>
  Java
</a></li><li class="dropdown-item"><!----> <a href="/categories/redis/" class="nav-link"><i class="iconfont undefined"></i>
  redis
</a></li><li class="dropdown-item"><!----> <a href="/categories/spring/" class="nav-link"><i class="iconfont undefined"></i>
  spring
</a></li><li class="dropdown-item"><!----> <a href="/categories/并发/" class="nav-link"><i class="iconfont undefined"></i>
  并发
</a></li><li class="dropdown-item"><!----> <a href="/categories/工作总结/" class="nav-link"><i class="iconfont undefined"></i>
  工作总结
</a></li><li class="dropdown-item"><!----> <a href="/categories/代码片段/" class="nav-link"><i class="iconfont undefined"></i>
  代码片段
</a></li><li class="dropdown-item"><!----> <a href="/categories/linux/" class="nav-link"><i class="iconfont undefined"></i>
  linux
</a></li><li class="dropdown-item"><!----> <a href="/categories/database/" class="nav-link"><i class="iconfont undefined"></i>
  database
</a></li><li class="dropdown-item"><!----> <a href="/categories/Travis/" class="nav-link"><i class="iconfont undefined"></i>
  Travis
</a></li></ul></div></div><div class="nav-item"><a href="/tag/" class="nav-link"><i class="iconfont reco-tag"></i>
  Tag
</a></div><div class="nav-item"><a href="/timeline/" class="nav-link"><i class="iconfont reco-date"></i>
  TimeLine
</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-message"></i>
      Contact
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="https://github.com/zhairuihao" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-github"></i>
  GitHub
  <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg></a></li></ul></div></div> <!----></nav></div></header> <div class="sidebar-mask" data-v-19557b78></div> <aside class="sidebar" data-v-19557b78><div class="personal-info-wrapper" data-v-b038cec6><img src="/avatar.jpg" alt="author-avatar" class="personal-img" data-v-b038cec6> <h3 class="name" data-v-b038cec6>
    zhairuihao
  </h3> <div class="num" data-v-b038cec6><div data-v-b038cec6><h3 data-v-b038cec6>15</h3> <h6 data-v-b038cec6>Article</h6></div> <div data-v-b038cec6><h3 data-v-b038cec6>17</h3> <h6 data-v-b038cec6>Tag</h6></div></div> <hr data-v-b038cec6></div> <nav class="nav-links"><div class="nav-item"><a href="/" class="nav-link"><i class="iconfont reco-home"></i>
  Home
</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-category"></i>
      Category
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/categories/JavaScript/" class="nav-link"><i class="iconfont undefined"></i>
  JavaScript
</a></li><li class="dropdown-item"><!----> <a href="/categories/Java/" class="nav-link"><i class="iconfont undefined"></i>
  Java
</a></li><li class="dropdown-item"><!----> <a href="/categories/redis/" class="nav-link"><i class="iconfont undefined"></i>
  redis
</a></li><li class="dropdown-item"><!----> <a href="/categories/spring/" class="nav-link"><i class="iconfont undefined"></i>
  spring
</a></li><li class="dropdown-item"><!----> <a href="/categories/并发/" class="nav-link"><i class="iconfont undefined"></i>
  并发
</a></li><li class="dropdown-item"><!----> <a href="/categories/工作总结/" class="nav-link"><i class="iconfont undefined"></i>
  工作总结
</a></li><li class="dropdown-item"><!----> <a href="/categories/代码片段/" class="nav-link"><i class="iconfont undefined"></i>
  代码片段
</a></li><li class="dropdown-item"><!----> <a href="/categories/linux/" class="nav-link"><i class="iconfont undefined"></i>
  linux
</a></li><li class="dropdown-item"><!----> <a href="/categories/database/" class="nav-link"><i class="iconfont undefined"></i>
  database
</a></li><li class="dropdown-item"><!----> <a href="/categories/Travis/" class="nav-link"><i class="iconfont undefined"></i>
  Travis
</a></li></ul></div></div><div class="nav-item"><a href="/tag/" class="nav-link"><i class="iconfont reco-tag"></i>
  Tag
</a></div><div class="nav-item"><a href="/timeline/" class="nav-link"><i class="iconfont reco-date"></i>
  TimeLine
</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-message"></i>
      Contact
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="https://github.com/zhairuihao" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-github"></i>
  GitHub
  <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg></a></li></ul></div></div> <!----></nav>  <ul class="sidebar-links"><li><section class="sidebar-group depth-0"><p class="sidebar-heading open"><span>树形结构数据存储方案</span> <!----></p> <!----></section></li></ul> </aside> <div class="password-shadow password-wrapper-in" style="display:none;" data-v-64685f0e data-v-19557b78><h3 class="title" style="display:none;" data-v-64685f0e data-v-64685f0e>树形结构数据存储方案</h3> <!----> <label id="box" class="inputBox" style="display:none;" data-v-64685f0e data-v-64685f0e><input type="password" value="" data-v-64685f0e> <span data-v-64685f0e>Konck! Knock!</span> <button data-v-64685f0e>OK</button></label> <div class="footer" style="display:none;" data-v-64685f0e data-v-64685f0e><span data-v-64685f0e><i class="iconfont reco-theme" data-v-64685f0e></i> <a target="blank" href="https://vuepress-theme-reco.recoluan.com" data-v-64685f0e>vuePress-theme-reco</a></span> <span data-v-64685f0e><i class="iconfont reco-copyright" data-v-64685f0e></i> <a data-v-64685f0e><span data-v-64685f0e>zhairuihao</span>
            
          <!---->
          2020
        </a></span></div></div> <div data-v-19557b78><main class="page"><div class="page-title" style="display:none;"><h1>树形结构数据存储方案</h1> <hr> <div data-v-484a899e><i class="iconfont reco-account" data-v-484a899e><span data-v-484a899e>zhairuihao</span></i> <i class="iconfont reco-date" data-v-484a899e><span data-v-484a899e>2017-08-26 12:00:00</span></i> <i class="iconfont reco-eye" data-v-484a899e><span id="/views/mysql/2017/2017-08-26-Nested-Sets.html" data-flag-title="Your Article Title" class="leancloud-visitors" data-v-484a899e><a class="leancloud-visitors-count" style="font-size:.9rem;font-weight:normal;color:#999;"></a></span></i> <i class="iconfont reco-tag tags" data-v-484a899e><span class="tag-item" data-v-484a899e>
      mysql
    </span><span class="tag-item" data-v-484a899e>
      数据结构
    </span></i></div></div> <div class="theme-reco-content content__default" style="display:none;"><blockquote><p><a href="http://blog.csdn.net/biplusplus/article/details/7433625" target="_blank" rel="noopener noreferrer">资料<svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg></a></p></blockquote> <h3 id="嵌套集"><a href="#嵌套集" class="header-anchor">#</a> 嵌套集</h3> <p><br>在基于数据库的一般应用中，查询的需求总要大于删除和修改。为了避免对于树形结构查询时的“递归”过程，基于Tree的前序遍历设计一种全新的无递归查询、无限分组的左右值编码方案，来保存该树的数据。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> Tree<span class="token punctuation">(</span>
    Node_id <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
    Name <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">600</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    Lft <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    rgt <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><ul><li>获取某个节点下的所有子孙节点</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Tree <span class="token keyword">WHERE</span> Lft <span class="token operator">&gt;</span> <span class="token number">2</span> <span class="token operator">AND</span> Lft <span class="token operator">&lt;</span> <span class="token number">11</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> Lft <span class="token keyword">ASC</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>获取子孙节点总数
<br>子孙总数 = (右值–左值–1)/2</li> <li>获取节点在树中所处的层数</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> Tree <span class="token keyword">WHERE</span> Lft <span class="token operator">&lt;=</span> <span class="token number">2</span> <span class="token operator">AND</span> Rgt <span class="token operator">&gt;=</span><span class="token number">11</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>获取当前节点所在路径</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Tree <span class="token keyword">WHERE</span> Lft <span class="token operator">&lt;=</span> <span class="token number">2</span> <span class="token operator">AND</span> Rgt <span class="token operator">&gt;=</span><span class="token number">11</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> Lft <span class="token keyword">ASC</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><br>在日常的处理中我们经常还会遇到的需要获取某一个节点的直属上级、同级、直属下级。为了更好的描述层级关系，我们可以为Tree建立一个视图，添加一个层次列，该列数值可以编写一个自定义函数来计算：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">FUNCTION</span> <span class="token punctuation">`</span>CountLayer<span class="token punctuation">`</span><span class="token punctuation">(</span><span class="token punctuation">`</span>_node_id<span class="token punctuation">`</span> <span class="token keyword">int</span><span class="token punctuation">)</span>
 <span class="token keyword">RETURNS</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
    <span class="token keyword">DECLARE</span> _result <span class="token keyword">INT</span><span class="token punctuation">;</span>
    <span class="token keyword">DECLARE</span> _lft <span class="token keyword">INT</span><span class="token punctuation">;</span>
    <span class="token keyword">DECLARE</span> _rgt <span class="token keyword">INT</span><span class="token punctuation">;</span>
    <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span><span class="token punctuation">(</span><span class="token keyword">SELECT</span> Node_id <span class="token keyword">FROM</span> Tree <span class="token keyword">WHERE</span> Node_id <span class="token operator">=</span> _node_id<span class="token punctuation">)</span>
    <span class="token keyword">THEN</span>
        <span class="token keyword">SELECT</span> Lft<span class="token punctuation">,</span>Rgt <span class="token keyword">FROM</span> Tree <span class="token keyword">WHERE</span> Node_id <span class="token operator">=</span> _node_id <span class="token keyword">INTO</span> _lft<span class="token punctuation">,</span>_rgt<span class="token punctuation">;</span>
        <span class="token keyword">SET</span> _result <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> Tree <span class="token keyword">WHERE</span> Lft <span class="token operator">&lt;=</span> _lft <span class="token operator">AND</span> Rgt <span class="token operator">&gt;=</span> _rgt<span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token keyword">RETURN</span> _result<span class="token punctuation">;</span>
    <span class="token keyword">ELSE</span>
        <span class="token keyword">RETURN</span> <span class="token number">0</span><span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>
<span class="token keyword">END</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br></div></div><p><br>在添加完函数以后，我们创建一个a视图，添加新的层次列：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">VIEW</span> <span class="token punctuation">`</span>NewView<span class="token punctuation">`</span><span class="token keyword">AS</span>
<span class="token keyword">SELECT</span> Node_id<span class="token punctuation">,</span> Name<span class="token punctuation">,</span> Lft<span class="token punctuation">,</span> Rgt<span class="token punctuation">,</span> CountLayer<span class="token punctuation">(</span>Node_id<span class="token punctuation">)</span> <span class="token keyword">AS</span> Layer <span class="token keyword">FROM</span> Tree <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> Lft <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><ul><li>获取当前节点父节点</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> treeview <span class="token keyword">WHERE</span> Lft <span class="token operator">&lt;=</span> <span class="token number">2</span> <span class="token operator">AND</span> Rgt <span class="token operator">&gt;=</span><span class="token number">11</span> <span class="token operator">AND</span> Layer<span class="token operator">=</span><span class="token number">1</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>获取所有兄弟节点</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> treeview <span class="token keyword">WHERE</span> Rgt <span class="token operator">&gt;</span> <span class="token number">11</span> <span class="token operator">AND</span> Rgt <span class="token operator">&lt;</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> Rgt <span class="token keyword">FROM</span> treeview <span class="token keyword">WHERE</span> Lft <span class="token operator">&lt;=</span> <span class="token number">2</span> <span class="token operator">AND</span> Rgt <span class="token operator">&gt;=</span><span class="token number">11</span> <span class="token operator">AND</span> Layer<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span> <span class="token operator">AND</span> Layer<span class="token operator">=</span><span class="token number">2</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>返回所有叶子节点</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Tree <span class="token keyword">WHERE</span> Rgt <span class="token operator">=</span> Lft <span class="token operator">+</span> <span class="token number">1</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>如何创建树如何新增数据
<br>上面已经介绍了如何检索结果，那么如何才能增加新的节点呢？Nested set 最重要是一定要有一个根节点作为所有节点的起点，而且通常这个节点是不被使用的。为了便于控制查询级别，在建表的时候建议添加parent_id配合之联结列表方式一起使用。</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">`</span>Tree<span class="token punctuation">`</span> <span class="token punctuation">(</span>
  <span class="token punctuation">`</span>node_id<span class="token punctuation">`</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
  <span class="token punctuation">`</span>parent_id<span class="token punctuation">`</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'0'</span><span class="token punctuation">,</span>
  <span class="token punctuation">`</span>name<span class="token punctuation">`</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  <span class="token punctuation">`</span>lft<span class="token punctuation">`</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'0'</span><span class="token punctuation">,</span>
  <span class="token punctuation">`</span>rgt<span class="token punctuation">`</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'0'</span><span class="token punctuation">,</span>
  <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>node_id<span class="token punctuation">`</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token keyword">KEY</span> <span class="token punctuation">`</span>idx_left_right<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>lft<span class="token punctuation">`</span><span class="token punctuation">,</span><span class="token punctuation">`</span>rgt<span class="token punctuation">`</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token punctuation">`</span>Tree<span class="token punctuation">`</span> <span class="token punctuation">(</span>parent_id<span class="token punctuation">,</span>name<span class="token punctuation">,</span>lft<span class="token punctuation">,</span>rgt<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span> <span class="token number">0</span><span class="token punctuation">,</span><span class="token string">'Food'</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>添加子节点（子节点起始处）</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">LOCK</span> <span class="token keyword">TABLE</span> Tree <span class="token keyword">WRITE</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token variable">@parent_id</span> :<span class="token operator">=</span> node_id<span class="token punctuation">,</span> <span class="token variable">@myLeft</span> :<span class="token operator">=</span> lft <span class="token keyword">FROM</span> Tree <span class="token keyword">WHERE</span> name <span class="token operator">=</span> <span class="token string">'Food'</span><span class="token punctuation">;</span>
<span class="token keyword">UPDATE</span> Tree <span class="token keyword">SET</span> rgt <span class="token operator">=</span> rgt <span class="token operator">+</span> <span class="token number">2</span> <span class="token keyword">WHERE</span> rgt <span class="token operator">&gt;</span> <span class="token variable">@myLeft</span><span class="token punctuation">;</span>
<span class="token keyword">UPDATE</span> Tree <span class="token keyword">SET</span> lft <span class="token operator">=</span> lft <span class="token operator">+</span> <span class="token number">2</span> <span class="token keyword">WHERE</span> lft <span class="token operator">&gt;</span> <span class="token variable">@myLeft</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> Tree<span class="token punctuation">(</span>parent_id<span class="token punctuation">,</span> name<span class="token punctuation">,</span> lft<span class="token punctuation">,</span> rgt<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token variable">@parent_id</span><span class="token punctuation">,</span> <span class="token string">'Fruit'</span><span class="token punctuation">,</span> <span class="token variable">@myLeft</span> <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token variable">@myLeft</span> <span class="token operator">+</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">UNLOCK</span> <span class="token keyword">TABLES</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><ul><li>在末尾追加</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">LOCK</span> <span class="token keyword">TABLE</span> Tree <span class="token keyword">WRITE</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token variable">@parent_id</span> :<span class="token operator">=</span> node_id <span class="token punctuation">,</span> <span class="token variable">@myRight</span> :<span class="token operator">=</span> rgt <span class="token keyword">FROM</span> Tree <span class="token keyword">WHERE</span> name <span class="token operator">=</span> <span class="token string">'Red'</span><span class="token punctuation">;</span>
<span class="token keyword">UPDATE</span> Tree <span class="token keyword">SET</span> rgt <span class="token operator">=</span> rgt <span class="token operator">+</span> <span class="token number">2</span> <span class="token keyword">WHERE</span> rgt <span class="token operator">&gt;=</span> <span class="token variable">@myRight</span><span class="token punctuation">;</span>
<span class="token keyword">UPDATE</span> Tree <span class="token keyword">SET</span> lft <span class="token operator">=</span> lft <span class="token operator">+</span> <span class="token number">2</span> <span class="token keyword">WHERE</span> lft <span class="token operator">&gt;</span> <span class="token variable">@myRight</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> Tree<span class="token punctuation">(</span>parent_id<span class="token punctuation">,</span> name<span class="token punctuation">,</span> lft<span class="token punctuation">,</span> rgt<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token variable">@parent_id</span><span class="token punctuation">,</span> <span class="token string">'Apple'</span><span class="token punctuation">,</span> <span class="token variable">@myRight</span><span class="token punctuation">,</span> <span class="token variable">@myRight</span> <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">UNLOCK</span> <span class="token keyword">TABLES</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><ul><li>在节点A后面添加同级节点</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">LOCK</span> <span class="token keyword">TABLE</span> Tree <span class="token keyword">WRITE</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token variable">@parent_id</span> :<span class="token operator">=</span> parent_id <span class="token punctuation">,</span> <span class="token variable">@myRight</span> :<span class="token operator">=</span> rgt <span class="token keyword">FROM</span> Tree <span class="token keyword">WHERE</span> name <span class="token operator">=</span> <span class="token string">'Yellow'</span><span class="token punctuation">;</span>
<span class="token keyword">UPDATE</span> Tree <span class="token keyword">SET</span> rgt <span class="token operator">=</span> rgt <span class="token operator">+</span> <span class="token number">2</span> <span class="token keyword">WHERE</span> rgt <span class="token operator">&gt;</span> <span class="token variable">@myRight</span><span class="token punctuation">;</span>
<span class="token keyword">UPDATE</span> Tree <span class="token keyword">SET</span> lft <span class="token operator">=</span> lft <span class="token operator">+</span> <span class="token number">2</span> <span class="token keyword">WHERE</span> lft <span class="token operator">&gt;</span> <span class="token variable">@myRight</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> Tree<span class="token punctuation">(</span>parent_id<span class="token punctuation">,</span> name<span class="token punctuation">,</span> lft<span class="token punctuation">,</span> rgt<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token variable">@parent_id</span><span class="token punctuation">,</span> <span class="token string">'Green'</span><span class="token punctuation">,</span> <span class="token variable">@myRight</span><span class="token operator">+</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token variable">@myRight</span><span class="token operator">+</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">UNLOCK</span> <span class="token keyword">TABLES</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><ul><li>删除节点（包含子节点）</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">LOCK</span> <span class="token keyword">TABLE</span> Tree <span class="token keyword">WRITE</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token variable">@myLeft</span> :<span class="token operator">=</span> lft <span class="token punctuation">,</span> <span class="token variable">@myRight</span> :<span class="token operator">=</span> rgt <span class="token keyword">FROM</span> Tree <span class="token keyword">WHERE</span> name <span class="token operator">=</span> <span class="token string">'Apple'</span><span class="token punctuation">;</span>
<span class="token keyword">DELETE</span> Tree <span class="token keyword">WHERE</span> lft <span class="token operator">&gt;=</span> <span class="token variable">@myLeft</span> <span class="token operator">AND</span> rgt <span class="token operator">&lt;=</span> <span class="token variable">@myRight</span><span class="token punctuation">;</span>
<span class="token keyword">UPDATE</span> Tree <span class="token keyword">SET</span> lft <span class="token operator">=</span> lft <span class="token operator">-</span> <span class="token punctuation">(</span><span class="token variable">@myRight</span> <span class="token operator">-</span> <span class="token variable">@myLeft</span><span class="token punctuation">)</span> <span class="token operator">-</span> <span class="token number">1</span> <span class="token keyword">WHERE</span> lft <span class="token operator">&gt;</span> <span class="token variable">@myRight</span><span class="token punctuation">;</span>
<span class="token keyword">UPDATE</span> Tree <span class="token keyword">SET</span> rgt <span class="token operator">=</span> rgt <span class="token operator">-</span> <span class="token punctuation">(</span><span class="token variable">@myRight</span> <span class="token operator">-</span> <span class="token variable">@myLeft</span><span class="token punctuation">)</span> <span class="token operator">-</span> <span class="token number">1</span> <span class="token keyword">WHERE</span> rgt <span class="token operator">&gt;</span> <span class="token variable">@myRight</span><span class="token punctuation">;</span>
<span class="token keyword">UNLOCK</span> <span class="token keyword">TABLES</span><span class="token punctuation">;</span>

<span class="token keyword">LOCK</span> <span class="token keyword">TABLE</span> Tree <span class="token keyword">WRITE</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token variable">@parent_id</span> :<span class="token operator">=</span> parent_id <span class="token punctuation">,</span> <span class="token variable">@node_id</span> :<span class="token operator">=</span>node_id <span class="token punctuation">,</span> <span class="token variable">@myLeft</span> :<span class="token operator">=</span> lft <span class="token punctuation">,</span> <span class="token variable">@myRight</span> :<span class="token operator">=</span> rgt <span class="token keyword">FROM</span> Tree <span class="token keyword">WHERE</span> name <span class="token operator">=</span> <span class="token string">'Red'</span><span class="token punctuation">;</span>
<span class="token keyword">UPDATE</span> Tree <span class="token keyword">SET</span> parent_id <span class="token operator">=</span> <span class="token variable">@parent_id</span> <span class="token keyword">WHERE</span>  parent_id <span class="token operator">=</span> <span class="token variable">@node_id</span>
<span class="token keyword">DELETE</span> Tree <span class="token keyword">WHERE</span> lft <span class="token operator">=</span> <span class="token variable">@myLeft</span><span class="token punctuation">;</span>
<span class="token keyword">UPDATE</span> Tree <span class="token keyword">SET</span> lft <span class="token operator">=</span> lft <span class="token operator">-</span> <span class="token number">1</span><span class="token punctuation">,</span>rgt <span class="token operator">=</span> rgt<span class="token operator">-</span><span class="token number">1</span> <span class="token keyword">Where</span> lft <span class="token operator">&gt;</span> <span class="token variable">@myLeft</span> <span class="token operator">AND</span> <span class="token variable">@rgt</span> <span class="token operator">&lt;</span> <span class="token variable">@myRight</span>
<span class="token keyword">UPDATE</span> Tree <span class="token keyword">SET</span> lft <span class="token operator">=</span> lft <span class="token operator">-</span> <span class="token number">2</span><span class="token punctuation">,</span>rgt <span class="token operator">=</span> rgt<span class="token operator">-</span><span class="token number">2</span> <span class="token keyword">Where</span> lft <span class="token operator">&gt;</span> <span class="token variable">@rgt</span> <span class="token operator">&gt;</span> <span class="token variable">@myRight</span>
<span class="token keyword">UNLOCK</span> <span class="token keyword">TABLES</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br></div></div><p><br>以上为Nested Set的CURD操作，具体在使用时建议结合事务和存储过程一起使用。本方案的优点时查询非常的方便，缺点就是每次插入删除数据涉及到的更新内容太多，如果树非常大，插入一条数据可能花很长的时间。</p> <h3 id="闭包表"><a href="#闭包表" class="header-anchor">#</a> 闭包表</h3> <p><br>将Closure Table翻译成闭包表不知道是否合适，闭包表的思路和物化路径差不多，都是空间换时间，Closure Table，一种更为彻底的全路径结构，分别记录路径上相关结点的全展开形式。能明晰任意两结点关系而无须多余查询，级联删除和结点移动也很方便。但是它的存储开销会大一些，除了表示结点的Meta信息，还需要一张专用的关系表</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> nodeInfo <span class="token punctuation">(</span>
    node_id <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    node_name <span class="token keyword">VARCHAR</span> <span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>node_id<span class="token punctuation">`</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span> <span class="token operator">=</span> utf8<span class="token punctuation">;</span>


<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> nodeRelationship <span class="token punctuation">(</span>
    ancestor <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    descendant <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    distance <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>ancestor<span class="token punctuation">,</span> descendant<span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span> <span class="token operator">=</span> utf8<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br></div></div><blockquote><p>其中
Ancestor代表祖先节点
Descendant代表后代节点
Distance 祖先距离后代的距离</p></blockquote> <ul><li>添加数据（创建存储过程）</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">DEFINER</span> <span class="token operator">=</span> <span class="token punctuation">`</span>root<span class="token punctuation">`</span><span class="token variable">@`localhost`</span> <span class="token keyword">PROCEDURE</span> <span class="token punctuation">`</span>AddNode<span class="token punctuation">`</span><span class="token punctuation">(</span><span class="token punctuation">`</span>_parent_name<span class="token punctuation">`</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">`</span>_node_name<span class="token punctuation">`</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
    <span class="token keyword">DECLARE</span> _ancestor <span class="token keyword">INT</span><span class="token punctuation">;</span>
    <span class="token keyword">DECLARE</span> _descendant <span class="token keyword">INT</span><span class="token punctuation">;</span>
    <span class="token keyword">DECLARE</span> _parent <span class="token keyword">INT</span><span class="token punctuation">;</span>
    <span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span><span class="token punctuation">(</span><span class="token keyword">SELECT</span> node_id <span class="token keyword">From</span> nodeinfo <span class="token keyword">WHERE</span> node_name <span class="token operator">=</span> _node_name<span class="token punctuation">)</span>
    <span class="token keyword">THEN</span>
        <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> nodeinfo <span class="token punctuation">(</span>node_name<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span>_node_name<span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token keyword">SET</span> _descendant <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> node_id <span class="token keyword">FROM</span> nodeinfo <span class="token keyword">WHERE</span> node_name <span class="token operator">=</span> _node_name<span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> noderelationship <span class="token punctuation">(</span>ancestor<span class="token punctuation">,</span>descendant<span class="token punctuation">,</span>distance<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span>_descendant<span class="token punctuation">,</span>_descendant<span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> node_id <span class="token keyword">FROM</span> nodeinfo <span class="token keyword">WHERE</span> node_name <span class="token operator">=</span> _parent_name<span class="token punctuation">)</span>
        <span class="token keyword">THEN</span>
            <span class="token keyword">SET</span> _parent <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> node_id <span class="token keyword">FROM</span> nodeinfo <span class="token keyword">WHERE</span> node_name <span class="token operator">=</span> _parent_name<span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> noderelationship <span class="token punctuation">(</span>ancestor<span class="token punctuation">,</span>descendant<span class="token punctuation">,</span>distance<span class="token punctuation">)</span> <span class="token keyword">SELECT</span> ancestor<span class="token punctuation">,</span>_descendant<span class="token punctuation">,</span>distance<span class="token operator">+</span><span class="token number">1</span> <span class="token keyword">from</span> noderelationship <span class="token keyword">where</span> descendant <span class="token operator">=</span> _parent<span class="token punctuation">;</span>
        <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>
<span class="token keyword">END</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br></div></div><ul><li>查询Fruit下所有的子节点</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span>
    n3<span class="token punctuation">.</span>node_name
<span class="token keyword">FROM</span>
    nodeinfo n1
<span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> noderelationship n2 <span class="token keyword">ON</span> n1<span class="token punctuation">.</span>node_id <span class="token operator">=</span> n2<span class="token punctuation">.</span>ancestor
<span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> nodeinfo n3 <span class="token keyword">ON</span> n2<span class="token punctuation">.</span>descendant <span class="token operator">=</span> n3<span class="token punctuation">.</span>node_id
<span class="token keyword">WHERE</span>
    n1<span class="token punctuation">.</span>node_name <span class="token operator">=</span> <span class="token string">'Fruit'</span>
<span class="token operator">AND</span> n2<span class="token punctuation">.</span>distance <span class="token operator">!=</span> <span class="token number">0</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><ul><li>查询Fruit下直属子节点</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span>
    n3<span class="token punctuation">.</span>node_name
<span class="token keyword">FROM</span>
    nodeinfo n1
<span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> noderelationship n2 <span class="token keyword">ON</span> n1<span class="token punctuation">.</span>node_id <span class="token operator">=</span> n2<span class="token punctuation">.</span>ancestor
<span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> nodeinfo n3 <span class="token keyword">ON</span> n2<span class="token punctuation">.</span>descendant <span class="token operator">=</span> n3<span class="token punctuation">.</span>node_id
<span class="token keyword">WHERE</span>
    n1<span class="token punctuation">.</span>node_name <span class="token operator">=</span> <span class="token string">'Fruit'</span>
<span class="token operator">AND</span> n2<span class="token punctuation">.</span>distance <span class="token operator">=</span> <span class="token number">1</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><ul><li>查询Fruit所处的层级</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span>
    n2<span class="token punctuation">.</span><span class="token operator">*</span><span class="token punctuation">,</span> n3<span class="token punctuation">.</span>node_name
<span class="token keyword">FROM</span>
    nodeinfo n1
<span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> noderelationship n2 <span class="token keyword">ON</span> n1<span class="token punctuation">.</span>node_id <span class="token operator">=</span> n2<span class="token punctuation">.</span>descendant
<span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> nodeinfo n3 <span class="token keyword">ON</span> n2<span class="token punctuation">.</span>ancestor <span class="token operator">=</span> n3<span class="token punctuation">.</span>node_id
<span class="token keyword">WHERE</span>
    n1<span class="token punctuation">.</span>node_name <span class="token operator">=</span> <span class="token string">'Fruit'</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span>
    n2<span class="token punctuation">.</span>distance <span class="token keyword">DESC</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><p>over</p></div> <footer class="page-edit" style="display:none;"><!----> <div class="last-updated"><span class="prefix">Last Updated: </span> <span class="time">5/15/2020, 10:55:03 AM</span></div></footer> <!----> <!----></main> <!----></div></div></div></div><div class="global-ui"><div class="back-to-ceiling" style="right:1rem;bottom:6rem;width:2.5rem;height:2.5rem;border-radius:.25rem;line-height:2.5rem;display:none;" data-v-44bd5a18 data-v-44bd5a18><svg t="1574745035067" viewBox="0 0 1024 1024" version="1.1" xmlns="http://www.w3.org/2000/svg" p-id="5404" class="icon" data-v-44bd5a18><path d="M526.60727968 10.90185116a27.675 27.675 0 0 0-29.21455937 0c-131.36607665 82.28402758-218.69155461 228.01873535-218.69155402 394.07834331a462.20625001 462.20625001 0 0 0 5.36959153 69.94390903c1.00431239 6.55289093-0.34802892 13.13561351-3.76865779 18.80351572-32.63518765 54.11355614-51.75690182 118.55860487-51.7569018 187.94566865a371.06718723 371.06718723 0 0 0 11.50484808 91.98906777c6.53300375 25.50556257 41.68394495 28.14064038 52.69160883 4.22606766 17.37162448-37.73630017 42.14135425-72.50938081 72.80769204-103.21549295 2.18761121 3.04276886 4.15646224 6.24463696 6.40373557 9.22774369a1871.4375 1871.4375 0 0 0 140.04691725 5.34970492 1866.36093723 1866.36093723 0 0 0 140.04691723-5.34970492c2.24727335-2.98310674 4.21612437-6.18497483 6.3937923-9.2178004 30.66633723 30.70611158 55.4360664 65.4791928 72.80769147 103.21549355 11.00766384 23.91457269 46.15860503 21.27949489 52.69160879-4.22606768a371.15156223 371.15156223 0 0 0 11.514792-91.99901164c0-69.36717486-19.13165746-133.82216804-51.75690182-187.92578088-3.42062944-5.66790279-4.76302748-12.26056868-3.76865837-18.80351632a462.20625001 462.20625001 0 0 0 5.36959269-69.943909c-0.00994388-166.08943902-87.32547796-311.81420293-218.6915546-394.09823051zM605.93803103 357.87693858a93.93749974 93.93749974 0 1 1-187.89594924 6.1e-7 93.93749974 93.93749974 0 0 1 187.89594924-6.1e-7z" p-id="5405" data-v-44bd5a18></path><path d="M429.50777625 765.63860547C429.50777625 803.39355007 466.44236686 1000.39046097 512.00932183 1000.39046097c45.56695499 0 82.4922232-197.00623328 82.5015456-234.7518555 0-37.75494459-36.9345906-68.35043303-82.4922232-68.34111062-45.57627738-0.00932239-82.52019037 30.59548842-82.51086798 68.34111062z" p-id="5406" data-v-44bd5a18></path></svg></div></div></div>
    <script src="/assets/js/app.0dee73fb.js" defer></script><script src="/assets/js/3.0d533a50.js" defer></script><script src="/assets/js/1.0c609987.js" defer></script><script src="/assets/js/25.533c713d.js" defer></script>
  </body>
</html>
